package com.sdjictec.haier.utils;

import com.sdjictec.haier.Kafka2Oracle;
import com.sdjictec.haier.config.ProFileConfig;
import com.sdjictec.haier.entity.RetailDataMQ;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.*;
import java.util.*;

public class DBUtills {

    private static Logger logger = LoggerFactory.getLogger(Kafka2Oracle.class);
    Properties props = new Properties();

    //tbsor 指第几个表
    public List jdbcsql(String sql, String tbsor) {


        System.out.println("开始连接数据库，读取信息");

        logger.info("开始连接数据库，读取信息");


        String driver = "";
        String user = "";
        String password = "";
        String url = "";


        if (tbsor.equals("tb")) {

            driver = ProFileConfig.driver;
            user = ProFileConfig.username;
            password = ProFileConfig.password;
            url = ProFileConfig.jdbcurl;

        } else if (tbsor.equals("stb")) {

            driver = ProFileConfig.seconddriver;
            user = ProFileConfig.secondusername;
            password = ProFileConfig.secondpassword;
            url = ProFileConfig.secondjdbcurl;

        } else {


            return null;
        }


        try {
            Class.forName(driver).newInstance();
        } catch (Exception e) {
            e.printStackTrace();
        }
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;

        System.out.println("start connect!");
        System.out.println("user:" + user);
        System.out.println("password:" + password);
        System.out.println("url:" + url);

        logger.info("user:" + user);
        logger.info("password:" + password);
        logger.info("url:" + url);


        try {
            conn = DriverManager.getConnection(url, user, password);
            System.out.println("connect!  sql exec");
            logger.info("connect!  sql exec");

            stmt = conn.createStatement();


            System.out.println(sql);

            logger.info(sql);

            rs = stmt.executeQuery(sql);
            // Iterate Over ResultSet


            List<Map<String, Object>> maps = new ArrayList<>();

            int columnnum = rs.getMetaData().getColumnCount();


            while (rs.next()) {

//                rs.getMetaData().getColumnCount();

//                rs.getLong()


                Map<String, Object> map = new HashMap<>();
                for (int i = 1; i <= columnnum; i++) {

                    String colname = rs.getMetaData().getColumnName(i);

                    System.out.println("colname:" + colname);


                    logger.info("colname:" + colname);

                    map.put(colname, rs.getObject(i));


                }


                maps.add(map);


            }


            return maps;
        } catch (SQLException e) {

            e.printStackTrace();
            System.out.println("SQL处理失败");


            logger.info("SQL处理失败");
            logger.error(e.getMessage());


        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

        }


        return null;
    }


    public boolean jdbcsave(RetailDataMQ retailDataMQ, String tablename) {


        System.out.println("开始连接表准备向T_RetailDataMQ/T_RetailDataMQ2写入数据");

        logger.info("开始连接表准备向T_RetailDataMQ/T_RetailDataMQ2写入数据");


        String driver = ProFileConfig.seconddriver;
        String user = ProFileConfig.secondusername;
        String password = ProFileConfig.secondpassword;
        String url = ProFileConfig.secondjdbcurl;

//        String tablename = "";

        if (tablename.equals("mq")) {
            tablename = "T_RETAIL_DATAMQ";
        } else if (tablename.equals("mq2")) {

            tablename = "T_RETAIL_DATAMQ2";
        }


        try {
            Class.forName(driver).newInstance();
        } catch (Exception e) {
            e.printStackTrace();
        }
        Connection conn = null;
        PreparedStatement ps = null;
//        Statement stmt = null;
//        ResultSet rs = null;

        System.out.println("start connect!");
        System.out.println("user:" + user);
        System.out.println("password:" + password);
        System.out.println("url:" + url);
        System.out.println("tablename:" + tablename);

        logger.info("user:" + user);
        logger.info("password:" + password);
        logger.info("url:" + url);
        logger.info("tablename:" + tablename);


        try {
            conn = DriverManager.getConnection(url, user, password);
            System.out.println("connect!  sql exec");


//            String sql = String.format("select count(*) as ct,min(%s) as minrsrm,max(%s) as maxrsrm  from %s", parcol, parcol, tablename);
            String sql = String.format("INSERT INTO %s (bill_uni_code,sp_code," +
                    "sh_uni_code,store_uni_code,sh_name,sh_address,sh_link_man,bill_type_code," +
                    "order_date,product_code,amount,brand_code,org_code,org_name,ib_price," +
                    "department,tmcsh_mktid,tmcsh_mainchannel,tx_dt) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)", tablename);

            ps = conn.prepareStatement(sql);
            System.out.println("start exe sql:" + sql);

            logger.info("start exe sql:" + sql);

            ps.setString(1, retailDataMQ.getBillUniCode());
            ps.setString(2, retailDataMQ.getSpCode());
            ps.setString(3, retailDataMQ.getShUniCode());
            ps.setString(4, retailDataMQ.getStoreUniCode());
            ps.setString(5, retailDataMQ.getShName());
            ps.setString(6, retailDataMQ.getShAddress());
            ps.setString(7, retailDataMQ.getShLinkMan());
            ps.setString(8, retailDataMQ.getBillTypeCode());

            ps.setTimestamp(9, retailDataMQ.getOrderDate());
            ps.setString(10, retailDataMQ.getProductCode());

            ps.setInt(11, retailDataMQ.getAmount());
            ps.setString(12, retailDataMQ.getBrandCode());
            ps.setString(13, retailDataMQ.getOrgCode());
            ps.setString(14, retailDataMQ.getOrgName());


            ps.setDouble(15, retailDataMQ.getIbPrice());
            ps.setString(16, retailDataMQ.getDepartment());
            ps.setString(17, retailDataMQ.getTmcshMktid());
            ps.setString(18, retailDataMQ.getTmcshMainchannel());
            ps.setDate(19, new java.sql.Date(retailDataMQ.getTx_dt().getTime()));

            ps.executeUpdate();

            return true;


        } catch (SQLException e) {
            e.printStackTrace();

            e.printStackTrace();
            System.out.println("入表处理失败");


            logger.info("入表处理失败");
            logger.error(e.getMessage());


            return false;

        } finally {
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

        }


    }


}

